Data check
Introduction
We present here some preliminary experiments in order to give an overview of the possibilities of analysis of world region based on the Food Balance Sheets (FBS) published by FAO. We import the data, realize some harmonization and check if our results are the same than the most recent report published by FAO FAO (2023).
Data collection
We use in a first step the most recent data produced by FAO, covering the period 2010 to present. We will further used another database covering the period 1963-2013 but not immediately as they are some difficulties for the harmonization of the two databases.
Geometry
We have prepared different geometry adapted to the 188 countries or territories available in the database.
Population
The FAO has collected variables from other sources (UN, Worldbank,…) in order to get estimates of the population of territorial units for which the FBS has been collected. We eliminate the unit called China (F351) because it is the sum of four other units present in the database China Mainland (CHN), Hong Kong (HKG), Macao (MAC) and … Taïwan (TWN). This duplication of data is clearly not practical from statistical point of view. But we can easily imagine why the republic of China has obliged the FAO to proceed this way …
Joining with `by = join_by(iso3)`
| iso3 | year | pop | name | region | continent |
|---|---|---|---|---|---|
| AFG | 2010 | 28189.67 | Afghanistan | Southern Asia | Asia |
| AFG | 2011 | 29249.16 | Afghanistan | Southern Asia | Asia |
| AFG | 2012 | 30466.48 | Afghanistan | Southern Asia | Asia |
| AFG | 2013 | 31541.21 | Afghanistan | Southern Asia | Asia |
| AFG | 2014 | 32716.21 | Afghanistan | Southern Asia | Asia |
| AFG | 2015 | 33753.50 | Afghanistan | Southern Asia | Asia |
The dataset is in long format but can easily be transformed in wide format :
| iso3 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AFG | 28189.67 | 29249.16 | 30466.48 | 31541.21 | 32716.21 | 33753.50 | 34636.21 | 35643.42 | 36686.78 | 37769.50 | 38972.23 | 40099.46 | 41128.77 |
| AGO | 23364.19 | 24259.11 | 25188.29 | 26147.00 | 27128.34 | 28127.72 | 29154.75 | 30208.63 | 31273.53 | 32353.59 | 33428.49 | 34503.77 | 35588.99 |
| ALB | 2913.40 | 2900.65 | 2892.19 | 2887.01 | 2884.10 | 2882.48 | 2881.06 | 2879.36 | 2877.01 | 2873.88 | 2866.85 | 2854.71 | 2842.32 |
| ARE | 8481.77 | 8575.20 | 8664.97 | 8751.85 | 8835.95 | 8916.90 | 8994.26 | 9068.30 | 9140.17 | 9211.66 | 9287.29 | 9365.15 | 9441.13 |
| ARG | 41100.12 | 41520.74 | 41952.36 | 42388.27 | 42824.05 | 43257.07 | 43668.24 | 44054.62 | 44413.60 | 44745.52 | 45036.03 | 45276.78 | 45510.32 |
| ARM | 2946.29 | 2928.98 | 2914.42 | 2901.39 | 2889.93 | 2878.59 | 2865.84 | 2851.92 | 2836.56 | 2820.60 | 2805.61 | 2790.97 | 2780.47 |
Some missing values can be observed but only for a limited number of territorial units :
`summarise()` has grouped output by 'iso3'. You can override using the
`.groups` argument.
| iso3 | name | available | max | pct |
|---|---|---|---|---|
| BHR | Bahrain | 4 | 13 | 30.8 |
| BTN | Bhutan | 4 | 13 | 30.8 |
| FSM | Micronesia (Federated States of) | 4 | 13 | 30.8 |
| MHL | Marshall Islands | 4 | 13 | 30.8 |
| NRU | Nauru | 4 | 13 | 30.8 |
| QAT | Qatar | 4 | 13 | 30.8 |
| SSD | South Sudan | 4 | 13 | 30.8 |
| TON | Tonga | 4 | 13 | 30.8 |
| PRK | Democratic People’s Republic of Korea | 9 | 13 | 69.2 |
| SDN | Sudan | 11 | 13 | 84.6 |
We elaborate a map of the average share of world population over the period :
Joining with `by = join_by(iso3)`
Food Balance Sheet (Kcal)
We select the Food Balance Sheet (FBS) measured in Kcal/capita/day which is one of the four possible option of measure. Initialy the table has a lot of columns :
Structure of data
| Domain Code | Domain | Area Code (ISO3) | Area | Element Code | Element | Item Code (FBS) | Item | Year Code | Year | Unit | Value | Flag | Flag Description | Note |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2010 | 2010 | kcal/cap/d | 1469.35 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2011 | 2011 | kcal/cap/d | 1407.04 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2012 | 2012 | kcal/cap/d | 1358.96 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2013 | 2013 | kcal/cap/d | 1390.57 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2014 | 2014 | kcal/cap/d | 1362.96 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2015 | 2015 | kcal/cap/d | 1371.68 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2016 | 2016 | kcal/cap/d | 1402.60 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2017 | 2017 | kcal/cap/d | 1405.12 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2018 | 2018 | kcal/cap/d | 1365.34 | E | Estimated value | NA |
| FBS | Food Balances (2010-) | AFG | Afghanistan | 664 | Food supply (kcal/capita/day) | S2511 | Wheat and products | 2019 | 2019 | kcal/cap/d | 1346.93 | E | Estimated value | NA |
We decide to simplify a bit the structure and to reduce the number of columns like this :
| year | iso3 | item | item_name | K |
|---|---|---|---|---|
| 2010 | AFG | S2511 | Wheat and products | 1469.35 |
| 2011 | AFG | S2511 | Wheat and products | 1407.04 |
| 2012 | AFG | S2511 | Wheat and products | 1358.96 |
| 2013 | AFG | S2511 | Wheat and products | 1390.57 |
| 2014 | AFG | S2511 | Wheat and products | 1362.96 |
| 2015 | AFG | S2511 | Wheat and products | 1371.68 |
| 2016 | AFG | S2511 | Wheat and products | 1402.60 |
| 2017 | AFG | S2511 | Wheat and products | 1405.12 |
| 2018 | AFG | S2511 | Wheat and products | 1365.34 |
| 2019 | AFG | S2511 | Wheat and products | 1346.93 |
Hierarchisation of food items
In the initial format delivered by FAO, the classification of items is a mixture of aggregates at different hierarchical level. Here we have decided to collect only the lowest level and to add a hierarchy of aggregation levels based on the table below :
| i0 | name_i0 | i1 | name_i1 | i2 | name_i2 | i | name_i |
|---|---|---|---|---|---|---|---|
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2511 | WHEAT & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2513 | BARLEY & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2514 | MAIZE & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2515 | RYE & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2516 | OATS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2517 | MILLET & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2518 | SORGHUM & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2520 | CEREALS, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2907 | STARCHY ROOTS | 2531 | POTATOES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2907 | STARCHY ROOTS | 2532 | CASSAVA & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2907 | STARCHY ROOTS | 2533 | SWEET POTATOES |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2907 | STARCHY ROOTS | 2534 | ROOTS, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2907 | STARCHY ROOTS | 2535 | YAMS |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2908 | SUGAR CROPS | 2536 | SUGAR CANE |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2908 | SUGAR CROPS | 2537 | SUGAR BEET |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2909 | SWEETENERS | 2541 | SUGAR NON-CENTRIFUGAL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2909 | SWEETENERS | 2542 | SUGAR (RAW EQUIVALENT) & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2909 | SWEETENERS | 2543 | SWEETENERS, NES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2911 | PULSES | 2546 | BEANS DRY |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2911 | PULSES | 2547 | PEAS DRY |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2911 | PULSES | 2549 | PULSES, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2912 | TREENUTS | 2551 | NUTS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2552 | GROUNDNUTS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2555 | SOYBEANS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2557 | SUNFLOWERSEED |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2558 | RAPE AND MUSTARDSEED & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2559 | COTTONSEED |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2560 | COCONUTS (INCL. COPRA) & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2561 | SESAMESEED |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2562 | PALM KERNELS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2563 | OLIVES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2913 | OILCROPS | 2570 | OILCROPS, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2571 | SOYBEAN OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2572 | GROUNDNUT OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2573 | SUNFLOWERSEED OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2574 | RAPE AND MUSTARD OIL & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2575 | COTTONSEED OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2576 | PALM KERNEL OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2577 | PALM OIL & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2578 | COPRA OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2579 | SESAMESEED OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2580 | OLIVE OIL & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2581 | RICE BRAN OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2582 | MAIZE GERM OIL |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2914 | VEGETABLE OILS | 2586 | OILCROPS OIL, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2918 | VEGETABLES | 2601 | TOMATOES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2918 | VEGETABLES | 2602 | ONIONS, DRY |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2918 | VEGETABLES | 2605 | VEGETABLES, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2611 | ORANGES AND MANDARINS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2612 | LEMONS AND LIMES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2613 | GRAPEFRUIT & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2614 | CITRUS, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2615 | BANANAS |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2616 | PLANTAINS |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2617 | APPLES (EXCL. CIDER) & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2618 | PINEAPPLES & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2619 | DATES |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2620 | GRAPES (EXCL. WINE) & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2919 | FRUITS (Excluding Wine) | 2625 | FRUIT, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2922 | STIMULANTS | 2630 | COFFEE & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2922 | STIMULANTS | 2633 | COCOA BEANS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2922 | STIMULANTS | 2635 | TEA & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2923 | SPICES | 2640 | PEPPER |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2923 | SPICES | 2641 | PIMENTO |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2923 | SPICES | 2642 | CLOVES |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2923 | SPICES | 2645 | SPICES, OTHER & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2924 | ALCOHOLIC BEVERAGES | 2655 | WINE & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2924 | ALCOHOLIC BEVERAGES | 2656 | BARLEY BEER |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2924 | ALCOHOLIC BEVERAGES | 2657 | BEVERAGES, FERMENTED & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2924 | ALCOHOLIC BEVERAGES | 2658 | BEVERAGES, ALCOHOLIC |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2924 | ALCOHOLIC BEVERAGES | 2659 | ALCOHOL, NON-FOOD |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2928 | MISCELLANEOUS | 2680 | INFANT FOOD |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2943 | MEAT | 2731 | BOVINE MEAT & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2943 | MEAT | 2732 | MUTTON/GOAT MEAT & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2943 | MEAT | 2733 | PIG MEAT & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2943 | MEAT | 2734 | POULTRY MEAT & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2943 | MEAT | 2735 | OTHER MEAT & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2945 | OFFALS | 2736 | OFFALS & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2946 | ANIMAL FATS | 2737 | FATS, ANIMAL, RAW & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2946 | ANIMAL FATS | 2740 | BUTTER, GHEE & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2946 | ANIMAL FATS | 2743 | CREAM |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2949 | EGGS | 2744 | EGGS & products |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2909 | SWEETENERS | 2745 | HONEY |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2761 | Freshwater Fish |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2762 | Demersal Fish |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2763 | Pelagic Fish |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2764 | Marine Fish, Other |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2765 | Crustaceans |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2766 | Cephalopods |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2767 | Molluscs, Other |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2961 | AQUATIC PRODUCTS | 2769 | Aquatic Animals, Others |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2961 | AQUATIC PRODUCTS | 2775 | Aquatic Plants |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2781 | Fish, Body Oil |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2960 | FISH SEAFOOD | 2782 | Fish, Liver Oil |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | 2807 | RICE & products |
| 2901 | TOTAL | 2941 | ANIMAL PRODUCTS | 2948 | MILK (excluding butter) | 2848 | MILK & products (excl. butter) |
| 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2928 | MISCELLANEOUS | 2899 | MISCELLANEOUS |
Synthetic data base
We decide to add the hiearachy of food items but also the hierarchy of geographical levels and the population in a single file where it will be possible to realize easily all types of analysis.
Joining with `by = join_by(i)`
Joining with `by = join_by(t, s)`
| s | t | name_s | i | name_i | Kist | Pst | i0 | name_i0 | i1 | name_i1 | i2 | name_i2 | reg_s | cont_s |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AFG | 2010 | Afghanistan | 2511 | Wheat and products | 1469.35 | 28189.67 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
| AFG | 2011 | Afghanistan | 2511 | Wheat and products | 1407.04 | 29249.16 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
| AFG | 2012 | Afghanistan | 2511 | Wheat and products | 1358.96 | 30466.48 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
| AFG | 2013 | Afghanistan | 2511 | Wheat and products | 1390.57 | 31541.21 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
| AFG | 2014 | Afghanistan | 2511 | Wheat and products | 1362.96 | 32716.21 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
| AFG | 2015 | Afghanistan | 2511 | Wheat and products | 1371.68 | 33753.50 | 2901 | TOTAL | 2903 | VEGETALE PRODUCTS | 2905 | CEREALS (excluding beer) | Southern Asia | Asia |
[1] "data.table" "data.frame"
As we can see the database is a “cube” with three elementary dimensions :
- \(t\) : the year of observation
- \(s\) : the state concerned
- \(i\) : the item of food at the elementary level.
Two quantitative indicators are available :
- \(F_{ijt}\) : the consumption of food from item \(i\) in state \(s\) during the year \(t\) measured in kcal/capita/day
- \(P_{it}\) : the estimated population of state \(i\) during the year \(t\) which can be used as weighting criteria for the procedure of agregation if we want to obtain corrected value at world, continental or regional level.
The following columns are different keys of aggregation for food items (i0, i1,i2) or for spatial units (reg, cont)
Data check
The synthetic database has 193650 lines which is less than the total number of value of the cube that should be equal to 96 food items x 189 countries x 13 years = 235872. It means that the matrix is filled at 82% but 18% of values are missing (no data for some countries some years) or equal to 0 (items that are not reported in one country).
Check 1 : total food supply
We can firstly try to compute some global values, for example in 2021.
World level
| Total Kcal | Total Population | Kcal/capita |
|---|---|---|
| 2.3377e+10 | 7850358 | 2977.826 |
- Comment : If our results are correct, the average food ration at world level (weighted by population of countries) is equal to 2978 kcal/capita/day. This value is based on 190 coutries that summarize a total of 7.850 billions of inhabitants. We have verified in the publication of FAO that it is exactly the figure published in the report of the organization FAO (2023).
Continental level
`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
| Continent | Kcal | Population | % Kcal | % Pop | Kcal/capita | index 100 = World |
|---|---|---|---|---|---|---|
| Africa | 3569201324 | 1386569 | 15.27 | 17.66 | 2574 | 86 |
| Asia | 13636915976 | 4650890 | 58.33 | 59.24 | 2932 | 98 |
| Oceania | 136859879 | 44165 | 0.59 | 0.56 | 3099 | 104 |
| Americas | 3467937046 | 1026254 | 14.83 | 13.07 | 3379 | 113 |
| Europe | 2566083186 | 742480 | 10.98 | 9.46 | 3456 | 116 |
- Comment : At continental level, we add some columns in order to compare for each continent the share of population and the share of food consumption measured in Kcal. According to our results, the lowest level of consumption is observed in Africa which account for 17.7% of world population but only 15.3% of food consumption mesured in Kcal. The level of consumption of Africa is at index 86 i.e -14% lower than the world average. On the contrary, Europe is at index 116 which is +16% higher than the world average. As inprevious case, we obtain the same figure than the FAO report FAO (2023).
Regional level
`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
| region | Kcal | Population | % Kcal | % Pop | Kcal/capita | index 100 = World |
|---|---|---|---|---|---|---|
| Eastern Africa | 1012076340 | 445491 | 4.33 | 5.67 | 2272 | 76 |
| Melanesia | 27842761 | 12189 | 0.12 | 0.16 | 2284 | 77 |
| Middle Africa | 458268899 | 199382 | 1.96 | 2.54 | 2298 | 77 |
| Southern Asia | 5111089355 | 1989452 | 21.86 | 25.34 | 2569 | 86 |
| Western Africa | 1109898236 | 418539 | 4.75 | 5.33 | 2652 | 89 |
| Southern Africa | 185122608 | 67985 | 0.79 | 0.87 | 2723 | 91 |
| Caribbean | 112474137 | 39486 | 0.48 | 0.50 | 2848 | 96 |
| South-Eastern Asia | 1940288698 | 669410 | 8.30 | 8.53 | 2899 | 97 |
| Polynesia | 1868998 | 629 | 0.01 | 0.01 | 2972 | 100 |
| Micronesia | 885953 | 297 | 0.00 | 0.00 | 2987 | 100 |
| South America | 1348459257 | 433953 | 5.77 | 5.53 | 3107 | 104 |
| Central America | 558610711 | 177662 | 2.39 | 2.26 | 3144 | 106 |
| Western Asia | 876043990 | 278406 | 3.75 | 3.55 | 3147 | 106 |
| Northern Africa | 803835241 | 255172 | 3.44 | 3.25 | 3150 | 106 |
| Central Asia | 239371457 | 75898 | 1.02 | 0.97 | 3154 | 106 |
| Eastern Europe | 971750272 | 290964 | 4.16 | 3.71 | 3340 | 112 |
| Eastern Asia | 5470122478 | 1637725 | 23.40 | 20.86 | 3340 | 112 |
| Northern Europe | 358664987 | 105888 | 1.53 | 1.35 | 3387 | 114 |
| Australia and New Zealand | 106262168 | 31051 | 0.45 | 0.40 | 3422 | 115 |
| Southern Europe | 530564557 | 150323 | 2.27 | 1.91 | 3530 | 119 |
| Western Europe | 705103371 | 195306 | 3.02 | 2.49 | 3610 | 121 |
| Northern America | 1448392941 | 375153 | 6.20 | 4.78 | 3861 | 130 |
- Comment : We can replicate the same analysis at the level of the 22 regions defined by UN as subdivisions of continents. We can reveal more important inequalities with a lowest level of 76 (-24% of world average) in Eastern Africa and an highest level of 130 (+30% of world average) in Northern America. These results are consistent with the publication of the FAO and the litterature on the subject.
Check 2 : food supply by commodity
As a second check, we will try to reproduce the figure below published in the last FAO report FAO (2023).
This example is interesting because FAO does not use the level of aggregation i2 (21 categories) described before but a more aggregated level (9 categories) that we propose to call i2b and store in our database as it is certainly more convenient in most of our future analysis. The aggregation suggested by FAO is decribed below :
| i2b | name_i2b | i2 | name_i2 |
|---|---|---|---|
| X01 | Cereals | 2905 | CEREALS (excluding beer) |
| X02 | Fats and oils | 2913 | OILCROPS |
| X02 | Fats and oils | 2914 | VEGETABLE OILS |
| X02 | Fats and oils | 2946 | ANIMAL FATS |
| X03 | Sugar | 2909 | SWEETENERS |
| X03 | Sugar | 2908 | SUGAR CROPS |
| X04 | Fruits and vegetables | 2918 | VEGETABLES |
| X04 | Fruits and vegetables | 2919 | FRUITS (Excluding Wine) |
| X05 | Root, tubers and pulses | 2907 | STARCHY ROOTS |
| X05 | Root, tubers and pulses | 2911 | PULSES |
| X06 | Meat | 2943 | MEAT |
| X06 | Meat | 2945 | OFFALS |
| X07 | Dairy and eggs (excl. butter) | 2949 | EGGS |
| X07 | Dairy and eggs (excl. butter) | 2948 | MILK (excluding butter) |
| X08 | Beverage and other | 2912 | TREENUTS |
| X08 | Beverage and other | 2922 | STIMULANTS |
| X08 | Beverage and other | 2923 | SPICES |
| X08 | Beverage and other | 2924 | ALCOHOLIC BEVERAGES |
| X08 | Beverage and other | 2928 | MISCELLANEOUS |
| X09 | Fish and seefod | 2960 | FISH SEAFOOD |
| X09 | Fish and seefod | 2961 | AQUATIC PRODUCTS |
So, we add this new key to our database and store it for further use.
Joining with `by = join_by(i2, name_i2)`
We can know try to build the figure of FAO report by agregating the data according to continents and item code i2b.
`summarise()` has grouped output by 's', 'cont_s', 'i2b'. You can override
using the `.groups` argument.
`summarise()` has grouped output by 'cont_s', 'i2b'. You can override using the
`.groups` argument.
Adding missing grouping variables: `cont_s`
| name | Africa | Americas | Asia | Europe | Oceania |
|---|---|---|---|---|---|
| X01 Cereals | 45.5 | 28.2 | 48.6 | 28.2 | 22.2 |
| X02 Fats and oils | 12.6 | 20.0 | 13.8 | 19.8 | 20.9 |
| X03 Sugar | 6.1 | 13.5 | 6.5 | 10.7 | 11.3 |
| X04 Fruits and vegetables | 6.5 | 6.0 | 8.6 | 6.0 | 7.3 |
| X05 Root, tubers and pulses | 20.0 | 5.3 | 5.4 | 4.4 | 7.8 |
| X06 Meat | 3.5 | 11.9 | 6.3 | 10.2 | 12.6 |
| X07 Dairy and eggs (excl. butter) | 2.9 | 9.7 | 6.1 | 12.4 | 8.7 |
| X08 Beverage and other | 2.2 | 4.8 | 3.0 | 7.1 | 8.0 |
| X09 Fish and seefod | 0.7 | 0.7 | 1.7 | 1.2 | 1.2 |
The table seems clearly in line with the figure of FAO report that we can try to replicate approximatively :
Check 3 : Map of evolution
Finally, we try to replicate a map of evolution of total food supply by state between 2018-2019 and 2020-2021, created by FAO in order to evaluate the effects of Covid crisis :
We can firsly produce the dataset :
`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
| state | Average 2018-19 | Average 2020-21 | Absolute variation |
|---|---|---|---|
| AFG | 2247 | 2253 | 6 |
| AGO | 2439 | 2400 | -40 |
| ALB | 3364 | 3340 | -24 |
| ARE | 3267 | 3307 | 41 |
| ARG | 3254 | 3327 | 73 |
| ARM | 3203 | 3222 | 19 |
Then we link the data with the map file by ISO3 code and realize the map using the same statistical breaks than FAO report :
The resulting map is the same than the one published by FAO. They are only differences concerning the country with missing values. Northern Korea and South Sudan are not avilable on our map but present in FAO report. The reverse is true for Somalia.
Conclusion
We have successfully created table, graphics and maps that are exactly the same than the one published by FAO (except very minor differences) and we can therefore conclude that the creation of the database and the linkage with map files is correct.
We will further enlarged the database to other indicators but the experiment on energy supply in Kcal is sufficient for the moment.
A more difficult challenge will be to produce long terme tome series from 1963 to present but this point will be discussed in another chapter of the website.